
import os
import pandas
import pymysql



def data_insert(filename):
    #从文件获取数据
    full_path = 'd:\\HS300\\' + filename
    df = pandas.read_csv(full_path)
    #连接mysql数据库
    db = pymysql.connect('localhost', 'root', 'minicooper', 'stock')
    #如果不存在，则创建表
    create_table_sql = "CREATE TABLE IF NOT EXISTS `{0}` (DATE DATE NOT NULL,OPEN FLOAT,HIGH FLOAT,CLOSE FLOAT,LOW FLOAT,VOLUME FLOAT,PRICE_CHANGE FLOAT,P_CHANGE FLOAT,MA5 FLOAT,MA10 FLOAT,MA20 FLOAT,V_MA5 FLOAT,V_MA10 FLOAT,V_MA20 FLOAT,TURNOVER FLOAT)".format(filename)
    #获取光标
    cursor = db.cursor()
    #执行操作
    cursor.execute(create_table_sql)
    print('table create is OK!')

    for i in range(len(df)):
        #获取每行数据
        daily_pickle = df[df.index == i]
        #获取单个数据
        date = daily_pickle['date'].values[0]
        open = daily_pickle['open'].values[0]
        high = daily_pickle['high'].values[0]
        close = daily_pickle['close'].values[0]
        low = daily_pickle['low'].values[0]
        volume = daily_pickle['volume'].values[0]
        price_change = daily_pickle['price_change'].values[0]
        p_change = daily_pickle['p_change'].values[0]
        ma5 = daily_pickle['ma5'].values[0]
        ma10 = daily_pickle['ma10'].values[0]
        ma20 = daily_pickle['ma20'].values[0]
        v_ma5 = daily_pickle['v_ma5'].values[0]
        v_ma10 = daily_pickle['v_ma10'].values[0]
        v_ma20 = daily_pickle['v_ma20'].values[0]
        turnover = daily_pickle['turnover'].values[0]
        #组合数据
        data = (date,open,high,close,low,volume,price_change,p_change,ma5,ma10,ma20,v_ma5,v_ma10,v_ma20,turnover)
        #插入表sql语言
        insert_table_sql = "INSERT INTO `{0}`(DATE,OPEN,HIGH,CLOSE,LOW,VOLUME,PRICE_CHANGE,P_CHANGE,MA5,MA10,MA20,V_MA5,V_MA10,V_MA20,TURNOVER) VALUES {1}".format(filename,data)
        try:
            #执行语句
            cursor.execute(insert_table_sql)
            #提交数据库执行
            db.commit()
        except:
            #如果发生错误则回滚
            db.rollback()
    print('{0} 导入完成'.format(filename))

    db.close()

dest = 'd:\\HS300\\'
for filename in os.listdir(dest):
    data_insert(filename)